"""
Case Type   : SQL语法/table
Case Name   : 验证列存唯一索引主键约束
Create At   : 2023/02/17
Owner       : cr13
Description :
    1.建表
    2.为表添加主键约束
    3.插入数据
    4.开启事务1,删除表数据回滚事务
    5.开启事务2,插入重复数据
    6.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.插入数据报错
    6.成功
History     :
"""

import os
import unittest
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class SQL(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info("-----------this is setup-----------")
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.constant = Constant()
        self.com = Common()
        self.commsh = CommonSH('PrimaryDbUser')
        self.tb_name = 't_keyword_prepare_0036'
        self.enable_delta_store = self.com.show_param(
            'enable_delta_store')
        result = self.commsh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG, 'enable_delta_store=on')
        self.assertTrue(result, '执行失败')
        result = self.commsh.restart_db_cluster()
        self.assertTrue(result, '执行失败')

    def test_column_table(self):
        text = "--step1:建表 expect:成功--"
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name} cascade;" \
            f"create table {self.tb_name}(a character varying(1024) not null," \
            f"b integer) with(orientation=column, deltarow_threshold=2, " \
            f"compression=low);"
        result = self.commsh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, result, '执行失败'
                      + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result, '执行失败'
                      + text)

        text = "--step2:为表添加主键约束 expect:成功--"
        self.log.info(text)
        sql_cmd = f"alter table {self.tb_name} add constraint t1_pkey " \
            f"primary key (a);"
        result = self.commsh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result, '执行失败' + text)

        text = "--step3:插入数据 expect:成功--"
        self.log.info(text)
        sql_cmd = f"insert into {self.tb_name} select generate_series(1,2);" \
            f"insert into {self.tb_name} select generate_series(3,4);" \
            f"insert into {self.tb_name} select generate_series(5,6);" \
            f"insert into {self.tb_name} select generate_series(7,8);" \
            f"insert into {self.tb_name} select generate_series(9,10);" \
            f"insert into {self.tb_name} select generate_series(11,12);" \
            f"insert into {self.tb_name} select generate_series(13,14);" \
            f"insert into {self.tb_name} select generate_series(15,16);" \
            f"insert into {self.tb_name} select generate_series(17,18);" \
            f"insert into {self.tb_name} select generate_series(19,20);" \
            f"insert into {self.tb_name} select generate_series(21,22);" \
            f"insert into {self.tb_name} select generate_series(23,24);" \
            f"insert into {self.tb_name} select generate_series(25,26);" \
            f"insert into {self.tb_name} select generate_series(27,28);" \
            f"insert into {self.tb_name} select generate_series(29,30);" \
            f"insert into {self.tb_name} select generate_series(31,32);" \
            f"insert into {self.tb_name} select generate_series(33,34);" \
            f"insert into {self.tb_name} select generate_series(35,36);" \
            f"insert into {self.tb_name} select generate_series(37,38);" \
            f"insert into {self.tb_name} select generate_series(39,40);" \
            f"insert into {self.tb_name} select generate_series(41,42);" \
            f"insert into {self.tb_name} select generate_series(43,44);" \
            f"insert into {self.tb_name} select generate_series(45,46);" \
            f"insert into {self.tb_name} select generate_series(47,48);" \
            f"insert into {self.tb_name} select generate_series(49,50);" \
            f"insert into {self.tb_name} select generate_series(51,52);" \
            f"insert into {self.tb_name} select generate_series(53,54);" \
            f"insert into {self.tb_name} select generate_series(55,56);" \
            f"insert into {self.tb_name} select generate_series(57,58);" \
            f"insert into {self.tb_name} select generate_series(59,60);" \
            f"insert into {self.tb_name} select generate_series(61,62);" \
            f"insert into {self.tb_name} select generate_series(63,64);" \
            f"insert into {self.tb_name} select generate_series(65,66);" \
            f"insert into {self.tb_name} select generate_series(67,68);"
        result = self.commsh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertEqual(result.count(self.constant.INSERT_SUCCESS_MSG), 34,
                         '执行失败' + text)

        text = "--step4:开启事务1,删除表数据回滚事务 expect:成功--"
        self.log.info(text)
        sql_cmd = f"start transaction;" \
            f"delete from {self.tb_name} where a in ('5','6');" \
            f"delete from {self.tb_name} where a in ('9','10');" \
            f"delete from {self.tb_name} where a in ('7','8');" \
            f"rollback;"
        result = self.commsh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG, result,
                      '执行失败' + text)
        self.assertEqual(result.count(self.constant.DELETE_SUCCESS_MSG), 3,
                         '执行失败' + text)
        self.assertIn(self.constant.ROLLBACK_MSG, result, '执行失败' + text)

        text = "--step5:开启事务2,插入重复数据 expect:插入数据报错--"
        self.log.info(text)
        sql_cmd = f"start transaction;" \
            f"insert into {self.tb_name} values(8,10);" \
            f"rollback;"
        result = self.commsh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG, result,
                      '执行失败' + text)
        self.assertIn('ERROR:  duplicate key value violates unique constraint '
                      '"t1_pkey"', result, '执行失败' + text)
        self.assertIn(self.constant.ROLLBACK_MSG, result, '执行失败' + text)

    def tearDown(self):
        text = "--step6:清理环境 expect:成功--"
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name} cascade;"
        drop_result = self.commsh.execut_db_sql(sql_cmd)
        self.log.info(drop_result)
        result1 = self.commsh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            f'enable_delta_store={self.enable_delta_store}')
        result2 = self.commsh.restart_db_cluster()
        self.assertTrue(result1, '执行失败')
        self.assertTrue(result2, '执行失败')
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_result, '执行失败'
                      + text)
        self.log.info(f"-----{os.path.basename(__file__)} end-----")
